#include "exportfiscal.h"
#include <common/constants.h>
#include <common/tablesdiagrams.h>
#include <QSqlQuery>
#include <QSqlError>
#include <QDir>
#include <QDebug>

using namespace Common;
using namespace Constants;
using namespace TablesDiagramsHonoraires;
using namespace TablesDiagramsMouvements;


ExportFiscal::ExportFiscal(QWidget * parent)
{
    Q_UNUSED(parent);
    setupUi(this);
    SEPARATEUR_DE_CHAMP = "|";
    ENCADREUR_DE_CHAMP = "\"";
    SEPARATEUR_DE_LIGNE = "\n";
    m_db = QSqlDatabase::database(Constants::DB_ACCOUNTANCY);
    initialiseDatas(); 
    connect(okButton,SIGNAL(pressed()),this,SLOT(choiceexportfile()));
    connect(quitButton,SIGNAL(pressed()),this,SLOT(close()));
}

ExportFiscal::~ExportFiscal(){}

void ExportFiscal::initialiseDatas()
{
    yearDateEdit->setDisplayFormat("yyyy");
    yearDateEdit->setDate(QDate::currentDate());
    QStringList usersList;
    usersList = users();
    tabUserComboBox->addItems(usersList);
    QStringList sitesList;
    sitesList = sites();
    tabSiteComboBox->addItems(sitesList);
    QStringList comptesList;
    comptesList = comptes();
    tabCompteComboBox->addItems(comptesList);
    QString fileexportedpath = QDir::homePath()+QDir::separator()+"exportation_fiscale";
    exportLineEdit->setText(fileexportedpath);
    choiceComboBox->addItem(tr("1-Résumé annuel"));
    choiceComboBox->addItem(tr("2-Toute la compta de l'année"));
}

QStringList ExportFiscal::users()
{
    enum {IDUSER = 0,LOGIN};
    QStringList list;
    QString requete = QString("select %1,%2 from %3").arg("id_usr","login","utilisateurs");
    QSqlQuery qy(m_db);
    if (!qy.exec(requete))
    {
    	qWarning() << __FILE__ << QString::number(__LINE__) << qy.lastError().text() ;
        }
    while (qy.next())
    {
    	list << qy.value(IDUSER).toString()+"|"+qy.value(LOGIN).toString();
        }
    return list;
}

QStringList ExportFiscal::sites()
{
    enum {IDSITE=0,SITE};
    QStringList list;
    QString requete = QString("select %1,%2 from %3").arg("id_site","site","sites");
    QSqlQuery qy(m_db);
    if (!qy.exec(requete))
    {
    	qWarning() << __FILE__ << QString::number(__LINE__) << qy.lastError().text() ;
        }
    while (qy.next())
    {
    	list << qy.value(IDSITE).toString()+"|"+qy.value(SITE).toString();
        }    
    return list;
}

QStringList ExportFiscal::comptes()
{
    enum {IDCOMPTE=0,LIBELLE};
    QStringList list;
    QString requete = QString("select %1,%2 from %3").arg("id_compte","libelle","comptes_bancaires");
    QSqlQuery qy(m_db);
    if (!qy.exec(requete))
    {
    	qWarning() << __FILE__ << QString::number(__LINE__) << qy.lastError().text() ;
        }
    while (qy.next())
    {
    	list << qy.value(IDCOMPTE).toString()+"|"+qy.value(LIBELLE).toString();
        }    
    return list;
}

void ExportFiscal::choiceexportfile()
{
    QString combotext = choiceComboBox->currentText();
    QString numberstr = combotext.split("-")[0];
    int number = numberstr.toInt();
    switch (number) {
        case 1 :
            exportResumeFiscalParPoste() ;
            break;
        case 2 :
            exportfile() ;
            break;
        default :
            break;
        }
}

void ExportFiscal::exportfile()
{
    enum ComboEnum {IDCOMBO=0,NAMECOMBO};
    enum TypeMouvementEnum {CREDIT_TYPE = 0, DEBIT_TYPE};
    QApplication::setOverrideCursor(QCursor(Qt::BusyCursor));
    QString exportationstring;
    QString idandlogin = tabUserComboBox->currentText();//"id_usr|login"
    QString idandsite = tabSiteComboBox->currentText();//"id_site|site"
    QString year = yearDateEdit->date().toString("yyyy");
    QString datebegin = year+"-01-01";
    QString dateend = year+"-12-31";
    QString exportfilepath;
    exportfilepath = exportLineEdit->text();
    QDir dir(exportfilepath);
    if (!dir.exists())
    {
        if(dir.mkpath(exportfilepath))
        {
            qWarning() << __FILE__ << QString::number(__LINE__) << exportfilepath << " has been created." ;
            }
        }
    QString identity = idandlogin+"_"+idandsite;
    identity.replace("?","e");
    identity.replace("*","a");
    identity.replace("|","_");
    QString exportfile = exportfilepath+QDir::separator()+identity+"_exportfiscal.txt";
    QFile fileforexport(exportfile);
    if (!fileforexport.open(QIODevice::WriteOnly))
    {
    	qWarning() << __FILE__ << QString::number(__LINE__) << "unable to open " << exportfile ;
    	}
    QTextStream stream(&fileforexport);
    stream << headersTax()+SEPARATEUR_DE_LIGNE;
    exportationstring += headersTax()+SEPARATEUR_DE_LIGNE;
    //inscrire recettes
    QString table = tablesHash().value(TablesSpace::HONORAIRES);
    QString idusercolumn = honorairesColumnsNamesHash().value(TablesDiagramsHonoraires::ID_USR);
    QString iduser = idandlogin.split("|")[IDCOMBO];
    QString idsitecolumn = honorairesColumnsNamesHash().value(TablesDiagramsHonoraires::ID_SITE);
    QString idsite = idandsite.split("|")[IDCOMBO];
    QString columns = honorairesColumns();
    QSqlQuery qy(m_db);
    QString reqrecettes = QString("select %1 from %2 where %3 = '%4' and %5 = '%6'")
                         .arg(columns,table,idusercolumn,iduser,idsitecolumn,idsite); 
    reqrecettes += QString(" and date between '%1' and '%2'").arg(datebegin,dateend);
    qDebug() << __FILE__ << QString::number(__LINE__) << reqrecettes ;
    QHash<int,int> hashdispatch = dispatchRecettesHash();
    if (!qy.exec(reqrecettes))
    {
    	qWarning() << __FILE__ << QString::number(__LINE__) << qy.lastError().text() ;
        }
    
    while (qy.next())
    {
    	//stream << SEPARATEUR_DE_CHAMP;
    	//exportationstring += SEPARATEUR_DE_CHAMP;
    	for (int exportcol = 1; exportcol < ExportFiscalEnumMaxParam; ++exportcol) 
    	{
    	        QString datastring;
    	        int idcolumnhono = hashdispatch.value(exportcol);
    	        if (idcolumnhono == -1)
    	        {
    	            datastring = QString();
    	            }
    	        else if (idcolumnhono == EXPORTFISCAL_CREDIT) //les recettes
    	        {
    	             datastring = QString::number(qy.value(TablesDiagramsHonoraires::ESP).toDouble()
    	                          +qy.value(TablesDiagramsHonoraires::CHQ).toDouble()
    	                          +qy.value(TablesDiagramsHonoraires::CB).toDouble()
    	                          +qy.value(TablesDiagramsHonoraires::VIR).toDouble()
    	                          +qy.value(TablesDiagramsHonoraires::AUTRE).toDouble()
    	                          +qy.value(TablesDiagramsHonoraires::DAF).toDouble()
    	                          +qy.value(TablesDiagramsHonoraires::DU).toDouble());
    	             datastring = montantavecvirgule(datastring);
    	        	}
    	        else if (idcolumnhono == EXPORTFISCAL_MODERGLT)
    	        {
    	                if (qy.value(TablesDiagramsHonoraires::ESP).toDouble()>0)
    	                {
    	                    datastring = "especes";
    	                    }
    	                else if (qy.value(TablesDiagramsHonoraires::CHQ).toDouble()>0)
    	                {
    	                    datastring = "cheque";
    	                    }
    	                else if (qy.value(TablesDiagramsHonoraires::CB).toDouble()>0)
    	                {
    	                    datastring = "carte bancaire";
    	                    }
    	                else if (qy.value(TablesDiagramsHonoraires::VIR).toDouble()>0)
    	                {
    	                    datastring = "virement";
    	                    }
    	                else if (qy.value(TablesDiagramsHonoraires::AUTRE).toDouble()>0)
    	                {
    	                    datastring = "autre";
    	                    }
    	                else if (qy.value(TablesDiagramsHonoraires::DAF).toDouble()>0)
    	                {
    	                    datastring = "dispense avance de frais";
    	                    }
    	                else if (qy.value(TablesDiagramsHonoraires::DU).toDouble()>0)
    	                {
    	                    datastring = "du";
    	                    }
    	                else{
    	                    qWarning() << __FILE__ << QString::number(__LINE__) << "error" ;
    	                    }
    	        	}
    	        else{
    	            datastring = qy.value(idcolumnhono).toString();
    	            }
    	        stream << ENCADREUR_DE_CHAMP << datastring << ENCADREUR_DE_CHAMP << SEPARATEUR_DE_CHAMP;
    	        exportationstring += ENCADREUR_DE_CHAMP+datastring+ENCADREUR_DE_CHAMP+SEPARATEUR_DE_CHAMP;
    		}//for
         stream << SEPARATEUR_DE_LIGNE;
         exportationstring +=SEPARATEUR_DE_LIGNE;
        }//while
    //inscrire depenses
    QString table_mouvements = tablesHash().value(TablesSpace::MOUVEMENTS);
    QSqlQuery qymvts(m_db);
    QString columnsmvts = mouvementsColumns();
    QString idandcompte = tabCompteComboBox->currentText();
    QString idcomptecolumn = mouvementsColumnsNamesHash().value(ID_COMPTE);
    QString idcompte = idandcompte.split("|")[IDCOMBO];
    QHash <int,int> hashdispatchmvts = dispatchMouvementsHash();
    QString reqmvts = QString("select %1 from %2 where %3 = '%4' and %5 = '%6'")
                         .arg(columnsmvts,table_mouvements,idusercolumn,iduser,idcomptecolumn,idcompte);
    reqmvts += QString(" and date between '%1' and '%2'").arg(datebegin,dateend);
    qDebug() << __FILE__ << QString::number(__LINE__) << reqmvts ;
    if (!qymvts.exec(reqmvts))
    {
        qWarning() << __FILE__ << QString::number(__LINE__) << qymvts.lastError().text();
        }
    while (qymvts.next())
    {
        //stream << SEPARATEUR_DE_CHAMP;
    	//exportationstring += SEPARATEUR_DE_CHAMP;
    	int typedebitoucredit = qymvts.value(TablesDiagramsMouvements::TYPE).toInt();
    	for (int exportcolumn = 1; exportcolumn < ExportFiscalEnumMaxParam; ++exportcolumn )
    	{
    	    QString datastring;
    	    int idcolumnmvt = hashdispatchmvts.value(exportcolumn);
    		if (idcolumnmvt == -1)
    		{
    		    datastring = QString();
    		    }
    		else if (idcolumnmvt == EXPORTFISCAL_CREDIT)
    		{
    		    if (typedebitoucredit == CREDIT_TYPE)
    		    {
    		        datastring = qymvts.value(TablesDiagramsMouvements::MONTANT).toString();
    		        datastring = montantavecvirgule(datastring);
    		        }
    		    else{
    		        datastring = QString();
    		        }
    		    }
    		else if (idcolumnmvt == EXPORTFISCAL_DEBIT)
    		{
    		    if (typedebitoucredit == DEBIT_TYPE)
    		    {
    		        datastring = qymvts.value(TablesDiagramsMouvements::MONTANT).toString();
    		        datastring = montantavecvirgule(datastring);
    		        }
    		    else{
    		        datastring = QString();
    		        }
    		    }
    		else
    		{
    		    datastring = qymvts.value(idcolumnmvt).toString();
    		    }
    		stream << ENCADREUR_DE_CHAMP << datastring << ENCADREUR_DE_CHAMP << SEPARATEUR_DE_CHAMP;
    	        exportationstring += ENCADREUR_DE_CHAMP+datastring+ENCADREUR_DE_CHAMP+SEPARATEUR_DE_CHAMP;
    		}//for
    	 stream << SEPARATEUR_DE_LIGNE;
         exportationstring +=SEPARATEUR_DE_LIGNE;
        }///while
    //
    textEdit->setPlainText(exportationstring);
    QApplication::restoreOverrideCursor();
}

QHash<int,int> ExportFiscal::dispatchRecettesHash()
{
    QHash<int,int> hash; 
    hash.insert(JOURNALCODE,-1);//lier columns recettes et columns export fiscal
    hash.insert(JOURNALLIB,-1);
    hash.insert(ECRITURENUM,ID_HONO);
    hash.insert(ECRITUREDATE,TablesDiagramsHonoraires::DATE);
    hash.insert(COMPTENUM,-1);
    hash.insert(COMPTELIB,TablesDiagramsHonoraires::ID_USR);
    hash.insert(COMPAUXNUM,-1);
    hash.insert(COMPAUXLIB,-1);
    hash.insert(PIECEREF,-1);
    hash.insert(PIECEDATE,-1);
    hash.insert(ECRITURELIB,ACTE);
    hash.insert(DEBIT,-1);
    hash.insert(CREDIT,EXPORTFISCAL_CREDIT);
    hash.insert(ECRITURELET,-1);
    hash.insert(DATELET,-1);
    hash.insert(VALIDDATE,-1);
    hash.insert(MONTANTDEVISE,-1);
    hash.insert(IDEVISE,-1);
    hash.insert(DATERGLT,TablesDiagramsHonoraires::DATE);
    hash.insert(MODERGLT,EXPORTFISCAL_MODERGLT);
    hash.insert(NATOP,-1);
    hash.insert(IDCLIENT,-1);
    return hash;    
}

QHash<int,int> ExportFiscal::dispatchMouvementsHash()
{
    QHash<int,int> hash;
    hash.insert(JOURNALCODE,-1);//lier columns mouvements et columns export fiscal
    hash.insert(JOURNALLIB,-1);
    hash.insert(ECRITURENUM,ID_MOUVEMENT);
    hash.insert(ECRITUREDATE,TablesDiagramsMouvements::DATE);
    hash.insert(COMPTENUM,-1);
    hash.insert(COMPTELIB,TablesDiagramsMouvements::ID_USR);
    hash.insert(COMPAUXNUM,-1);
    hash.insert(COMPAUXLIB,-1);
    hash.insert(PIECEREF,-1);
    hash.insert(PIECEDATE,-1);
    hash.insert(ECRITURELIB,LIBELLE);
    hash.insert(DEBIT,EXPORTFISCAL_DEBIT);
    hash.insert(CREDIT,EXPORTFISCAL_CREDIT);
    hash.insert(ECRITURELET,-1);
    hash.insert(DATELET,-1);
    hash.insert(VALIDDATE,DATE_VALEUR);
    hash.insert(MONTANTDEVISE,-1);
    hash.insert(IDEVISE,-1);
    hash.insert(DATERGLT,TablesDiagramsMouvements::DATE);
    hash.insert(MODERGLT,TablesDiagramsMouvements::REMARQUE);
    hash.insert(NATOP,TablesDiagramsMouvements::DETAIL);
    hash.insert(IDCLIENT,-1);
    return hash;
}

QString ExportFiscal::honorairesColumns()
{
    QString columns;
    QStringList list;
    QHash<int,QString> hash;
    hash = honorairesColumnsNamesHash();
    for (int i = 0; i < hash.count(); ++i)
    {
    	list << hash.value(i);
        }
    columns = list.join(",");
    return columns;
}

QString ExportFiscal::mouvementsColumns()
{
    QString columns;
    QStringList list;
    QHash<int,QString> hash;
    hash = mouvementsColumnsNamesHash();
    for (int i = 0; i < hash.count(); ++i)
    {
    	list << hash.value(i);
        }
    columns = list.join(",");
    return columns;
}

QString ExportFiscal::headersTax()
{
    enum taxExportHeadersEnum {NUM=0,INFORMATION,NOM_DU_CHAMP,TYPE_DE_CHAMP};    
    QString headers;
    //headers += SEPARATEUR_DE_CHAMP;
    QStringList lines = QString(tableauCodesExportationFiscale).split("$");
    foreach(QString line,lines)
    {
        QStringList listinline;
        listinline = line.split("|");
        QString nomduchamp = listinline[NOM_DU_CHAMP];
        if (nomduchamp == QString("NOM DU CHAMP"))
        {
            continue;
            }
        headers += ENCADREUR_DE_CHAMP+nomduchamp+ENCADREUR_DE_CHAMP+SEPARATEUR_DE_CHAMP;
        }
    return headers;
}

QString ExportFiscal::montantavecvirgule(QString & montant)
{
    QString result;
    if (montant.contains(","))
    {
        result = montant;
        }
    else if (montant.contains("."))
    {
        result = montant.replace(".",",");
        }
    else{
        result = montant+",00";
        }
    return result;
}

void ExportFiscal::exportResumeFiscalParPoste()
{
    enum ComboEnum {IDCOMBO=0,NAMECOMBO};
    enum TypeMouvementEnum {CREDIT_TYPE = 0, DEBIT_TYPE};
    QString exportstring;
    exportstring += headersTax()+SEPARATEUR_DE_LIGNE;
    QString idandlogin = tabUserComboBox->currentText();//"id_usr|login"
    QString idandsite = tabSiteComboBox->currentText();//"id_site|site"
    //RECETTES PAR MOIS
    QString table = tablesHash().value(TablesSpace::HONORAIRES);
    QString idusercolumn = honorairesColumnsNamesHash().value(TablesDiagramsHonoraires::ID_USR);
    QString iduser = idandlogin.split("|")[IDCOMBO];
    QString idsitecolumn = honorairesColumnsNamesHash().value(TablesDiagramsHonoraires::ID_SITE);
    QString idsite = idandsite.split("|")[IDCOMBO];
    QString year = yearDateEdit->date().toString("yyyy");
    QString columns = honorairesColumns();    
    QString reqrecettes = QString("select %1 from %2 where %3 = '%4' and %5 = '%6'")
                         .arg(columns,table,idusercolumn,iduser,idsitecolumn,idsite);
    for (int month = 1; month < 13; ++month)
    {
        QHash<QString,double> hash;
        reqrecettes += whereDateBetweenSqlHash(year).value(month);
        QSqlQuery qy(m_db);
        if (!qy.exec(reqrecettes))
        {
            qWarning() << __FILE__ << QString::number(__LINE__) << qy.lastError().text() ;
            }
        while (qy.next())
        {
            QString libelle = qy.value(ACTE).toString();
            double amount = qy.value(ESP).toDouble()+qy.value(CHQ).toDouble()+qy.value(CB).toDouble()+qy.value(VIR).toDouble()+qy.value(AUTRE).toDouble();
            hash.insert(libelle,amount);            
            }
         //TODO LIRE HASH ECRITURE CREDIT POUR REGROUPEMENT PAR TYPE DE RECETTES  
         QStringList keyslibellelist;
         keyslibellelist = hash.keys();
         keyslibellelist.removeDuplicates();
         foreach(QString key,keyslibellelist)
         {
             double total = 0.00;
             QList<double> amountslist;
             amountslist = hash.values(key);
             for (int i = 0; i < amountslist.size(); ++i )
             {
             	total += amountslist[i];
             	}
             for (int exportcolumn = 1; exportcolumn < ExportFiscalEnumMaxParam; ++exportcolumn )
             {
             	if (exportcolumn == ECRITURELIB)
             	{
             	    exportstring += ENCADREUR_DE_CHAMP+key+ENCADREUR_DE_CHAMP+SEPARATEUR_DE_CHAMP;
             	    }
             	else if (exportcolumn == CREDIT)
             	{
             	    QString amountstring = montantavecvirgule(amountstring);
             	    exportstring += ENCADREUR_DE_CHAMP+amountstring+ENCADREUR_DE_CHAMP+SEPARATEUR_DE_CHAMP;
             	    }
             	else{
             	    exportstring += ENCADREUR_DE_CHAMP+QString()+ENCADREUR_DE_CHAMP+SEPARATEUR_DE_CHAMP;
             	    }             	
             	}
             exportstring +=SEPARATEUR_DE_LIGNE;
             }         
    }//for
    textEdit->setPlainText(exportstring);
}

QHash<int,QString> ExportFiscal::whereDateBetweenSqlHash(QString year)
{
        //QDate yeardate = QDate::fromString(year,"yyyy");
        QHash<int,QString> hash;
        hash.insert(1,"where date between '"+year+"-01-01' and '"+year+"-01-31'");
        if (QDate::isLeapYear(year.toInt()))
        {
            hash.insert(2,"where date between '"+year+"-02-01' and '"+year+"-02-29'");
            }
        else{
            hash.insert(2,"where date between '"+year+"-02-01' and '"+year+"-02-28'");
            }      
        hash.insert(3,"where date between '"+year+"-03-01' and '"+year+"-03-31'");
        hash.insert(4,"where date between '"+year+"-04-01' and '"+year+"-04-30'");
        hash.insert(5,"where date between '"+year+"-05-01' and '"+year+"-05-31'");
        hash.insert(6,"where date between '"+year+"-06-01' and '"+year+"-060'");
        hash.insert(7,"where date between '"+year+"-07-01' and '"+year+"-07-31'");
        hash.insert(8,"where date between '"+year+"-08-01' and '"+year+"-08-31'");
        hash.insert(9,"where date between '"+year+"-09-01' and '"+year+"-09-30'");
        hash.insert(10,"where date between '"+year+"-10-01' and '"+year+"-10-31'");
        hash.insert(11,"where date between '"+year+"-11-01' and '"+year+"-11-30'");
        hash.insert(12,"where date between '"+year+"-12-01' and '"+year+"-12-31'");
        return hash;
}
